package com.ciwong.epaper.modules.me.dao.db.db;

import com.ciwong.epaper.modules.me.dao.db.helper.StudyRecordHelper;
import com.ciwong.epaper.modules.me.dao.db.table.StudyRecordTable;
import com.ciwong.epaper.modules.epaper.bean.Answer;
import com.ciwong.epaper.modules.epaper.bean.ModuleInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

/**
 * 我的学习记录数据库
 *
 * @author res-qqlin
 * @version ciwong v.2.0 2015/7/29
 * @since ciwong v.2.0
 */
public class StudyRecordDB
{
    public static final String TAG = StudyRecordDB.class.getSimpleName();

    private static Context mContext;

    private static SQLiteDatabase db;

    private static StudyRecordHelper helper;

    private static final String SYNC_TAG = "SYNC_TAG";

    public static void setContext(Context context)
    {
        mContext = context;
    }

    /**
     * 获取读写数据库
     *
     */
    private static void getWritableDB()
    {
        if (helper == null)
        {
            helper = new StudyRecordHelper(mContext);
        }
        db = helper.getWritableDatabase();
    }

    /**
     * 获取可读数据库
     */
    private static void getReadableDB()
    {
        if (helper == null)
        {
            helper = new StudyRecordHelper(mContext);
        }
        db = helper.getReadableDatabase();
    }

    /**
     * 关闭数据库
     */
    public static void dispose()
    {
        if (db != null)
        {
            db.close();
        }
        if (helper != null)
        {
            helper.close();
        }
    }

    private static Answer getMyWork(Cursor cursor)
    {
        Answer answer = new Answer();
        answer.set_id(
                cursor.getLong(cursor.getColumnIndex(StudyRecordTable._ID)));
        answer.setUserId(cursor
                .getLong(cursor.getColumnIndex(StudyRecordTable.USER_ID)));
        answer.setUserName(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.USER_NAME)));
        answer.setPackageId(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.PACKAGE_ID)));
        answer.setcId(
                cursor.getString(cursor.getColumnIndex(StudyRecordTable.CID)));
        answer.setModuleId(cursor
                .getInt(cursor.getColumnIndex(StudyRecordTable.MODULE_ID)));
        answer.setVersionId(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.VERSION_ID)));
        answer.setResourceName(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.RESOURCE_NAME)));
        answer.setParentVersionId(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.PARENT_VERSION_ID)));
        answer.setResourceType(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.RESOURCE_TYPE)));
        answer.setWorkId(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.WORK_ID)));
        answer.setWorkAnswers(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.WORK_ANSWERS)));
        answer.setActualScore(cursor.getFloat(
                cursor.getColumnIndex(StudyRecordTable.ACTUAL_SCORE)));
        answer.setWorkLong(cursor
                .getInt(cursor.getColumnIndex(StudyRecordTable.WORK_LONG)));
        answer.setDoWorkPackageUrl(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.DO_WORK_PACKAGE_URL)));
        answer.setDoWorkLocalPath(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.DO_WORK_LOCAL_PATH)));
        answer.setWorkStatus(cursor
                .getInt(cursor.getColumnIndex(StudyRecordTable.WORK_STATUS)));
        answer.setSubmitDate(cursor
                .getLong(cursor.getColumnIndex(StudyRecordTable.SUBMIT_DATE)));
        answer.setSubmitStatus(cursor
                .getInt(cursor.getColumnIndex(StudyRecordTable.SUBMIT_STATUS)));
        answer.setCreateTime(cursor
                .getLong(cursor.getColumnIndex(StudyRecordTable.CREATE_TIME)));
        answer.setCodeId(cursor
                .getLong(cursor.getColumnIndex(StudyRecordTable.CODE_ID)));
        answer.setLogInfo(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.LOG_INFO)));
        answer.setComment(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.COMMENT)));
        answer.setClassId(cursor
                .getString(cursor.getColumnIndex(StudyRecordTable.CLASS_ID)));
        answer.setWorkScore(cursor
                .getFloat(cursor.getColumnIndex(StudyRecordTable.WORK_SCORE)));
        answer.setOldDoWorkId(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.OLD_DO_WORK_ID)));
        answer.setBrandId(cursor
                .getInt(cursor.getColumnIndex(StudyRecordTable.BRAND_ID)));
        answer.setCheckedResource(cursor.getString(
                cursor.getColumnIndex(StudyRecordTable.CHECKED_RESOURCE)));
        return answer;
    }

    private static ContentValues getContentValues(Answer answer)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(StudyRecordTable.USER_ID, answer.getUserId());
        contentValues.put(StudyRecordTable.USER_NAME, answer.getUserName());
        contentValues.put(StudyRecordTable.PACKAGE_ID, answer.getPackageId());
        contentValues.put(StudyRecordTable.CID, answer.getcId());
        contentValues.put(StudyRecordTable.MODULE_ID, answer.getModuleId());
        contentValues.put(StudyRecordTable.VERSION_ID, answer.getVersionId());
        contentValues.put(StudyRecordTable.RESOURCE_NAME,
                answer.getResourceName());
        contentValues.put(StudyRecordTable.PARENT_VERSION_ID,
                answer.getParentVersionId());
        contentValues.put(StudyRecordTable.RESOURCE_TYPE,
                answer.getResourceType());
        contentValues.put(StudyRecordTable.WORK_ID, answer.getWorkId());
        contentValues.put(StudyRecordTable.WORK_ANSWERS,
                answer.getWorkAnswers());
        contentValues.put(StudyRecordTable.ACTUAL_SCORE,
                answer.getActualScore());
        contentValues.put(StudyRecordTable.WORK_LONG, answer.getWorkLong());

        contentValues.put(StudyRecordTable.DO_WORK_PACKAGE_URL,
                answer.getDoWorkPackageUrl());
        contentValues.put(StudyRecordTable.DO_WORK_LOCAL_PATH,
                answer.getDoWorkLocalPath());
        contentValues.put(StudyRecordTable.WORK_STATUS, answer.getWorkStatus());
        contentValues.put(StudyRecordTable.SUBMIT_DATE, answer.getSubmitDate());
        contentValues.put(StudyRecordTable.SUBMIT_STATUS,
                answer.getSubmitStatus());
        contentValues.put(StudyRecordTable.CREATE_TIME, answer.getCreateTime());
        contentValues.put(StudyRecordTable.CODE_ID, answer.getCodeId());
        contentValues.put(StudyRecordTable.LOG_INFO, answer.getLogInfo());
        contentValues.put(StudyRecordTable.COMMENT, answer.getComment());
        contentValues.put(StudyRecordTable.WORK_SCORE, answer.getWorkScore());
        contentValues.put(StudyRecordTable.CLASS_ID, answer.getClassId());
        contentValues.put(StudyRecordTable.OLD_DO_WORK_ID,
                answer.getOldDoWorkId());
        contentValues.put(StudyRecordTable.BRAND_ID, answer.getBrandId());
        contentValues.put(StudyRecordTable.CHECKED_RESOURCE,
                answer.getCheckedResource());
        return contentValues;
    }

    /**
     * 插入数据
     *
     * @param answer
     *            需要插入或更新的答案对象
     *
     * @return 返回Cursor第一条数据
     */
    public static long insertWorkData(final Answer answer)
    {
        String unique = StudyRecordTable._ID;
        String value = answer.get_id() + "";
        long id = 0;
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            Cursor cur = db.query(StudyRecordTable.TABLE_NAME,
                    new String[]{StudyRecordTable._ID}, unique + " = ?",
                    new String[]{value}, null, null, null);

            if (cur.getCount() > 0)
            {
                cur.moveToFirst();
                id = cur.getLong(0);
                db.update(StudyRecordTable.TABLE_NAME, getContentValues(answer),
                        unique + " = ?", new String[]{value});
            }
            else
            {
                id = db.insert(StudyRecordTable.TABLE_NAME, null,
                        getContentValues(answer));
            }
            cur.close();
            dispose();
        }
        return id;
    }

    /**
     * 修改作业的状态
     * 
     * @param status
     * @param _id
     */
    public static synchronized void updateWorkStatus(int status, long _id)
    {

        getWritableDB();
        db.execSQL("update " + StudyRecordTable.TABLE_NAME + " set "
                + StudyRecordTable.WORK_STATUS + " = " + status + " where "
                + StudyRecordTable._ID + " = " + _id);
        dispose();
    }

    /**
     * 修改作业的提交状态
     *
     * @param status
     * @param _id
     */
    public static void updateWorkSubmitStatus(int status, long _id)
    {
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            db.execSQL("update " + StudyRecordTable.TABLE_NAME + " set "
                    + StudyRecordTable.SUBMIT_STATUS + " = " + status
                    + " where " + StudyRecordTable._ID + " = " + _id);
            dispose();
        }
    }

    /**
     * 查询数据列表
     * 
     * @param lastId
     * @param pageSize
     * @return
     */
    public static synchronized ArrayList<Answer> queryMyWorkListByPage(
            long lastId, int pageSize)
    {
        ArrayList<Answer> workList = new ArrayList<Answer>();

        getWritableDB();
        Cursor cur = null;

        String sql = "select * from " + StudyRecordTable.TABLE_NAME
                + " order by " + StudyRecordTable.SUBMIT_DATE
                + " desc limit 0, ? ";

        cur = db.rawQuery(sql, new String[]{pageSize + ""});

        if (cur != null && cur.getCount() > 0)
        {
            while (cur.moveToNext())
            {
                workList.add(getMyWork(cur));
            }
        }
        cur.close();
        dispose();
        return workList;
    }

    /**
     * 根据做作业id删除单条数据
     * 
     * @param doWorkId
     */
    public static void deleteDataByDoWorkId(int doWorkId)
    {
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            db.delete(StudyRecordTable.TABLE_NAME,
                    StudyRecordTable.DO_WORK_ID + " = ?",
                    new String[]{doWorkId + ""});
            dispose();
        }
    }

    /**
     * 根据做_id删除单条数据
     *
     * @param _Id
     */
    public static void deleteDataById(String _Id)
    {
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            db.delete(StudyRecordTable.TABLE_NAME,
                    StudyRecordTable._ID + " = ?", new String[]{_Id});
            dispose();
        }
    }

    /**
     * 删除已完成作业记录
     *
     * @param isOnline
     *            是否是线上作业
     */
    public static void deleteAllCompleted(int isOnline)
    {
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            db.delete(StudyRecordTable.TABLE_NAME,
                    StudyRecordTable.SUBMIT_STATUS + " = ? and "
                            + StudyRecordTable.MODULE_ID
                            + (isOnline == 0 ? " = ?" : "!= ?"),
                    new String[]{Answer.SubmitStatus.WORK_SUBMIT_SUCCUED + "",
                            ModuleInfo.ModuleInfoId.MODULE_ID_TEST + ""});
            dispose();
        }
    }

    /**
     * 刷新已完成作业列表
     *
     * @param isOnline
     *            是否是线上作业
     * @param answers
     *            需要插入或更新的答案对象列数据
     * @return 返回Cursor第一条数据
     */
    public static void reFillCompeletedList(int isOnline,
            final List<Answer> answers)
    {
        synchronized (SYNC_TAG)
        {
            deleteAllCompleted(isOnline);
            if (answers != null)
            {
                for (int i = 0; i < answers.size(); i++)
                {
                    Answer answer = answers.get(i);
                    if (answer != null)
                    {
                        getWritableDB();
                        db.insert(StudyRecordTable.TABLE_NAME, null,
                                getContentValues(answer));
                        dispose();
                    }
                }
            }
        }
    }

    /**
     * 查询数据列表正在提交的 和 已提交的
     * 
     * @return
     */
    public static ArrayList<Answer> queryMyWorkListBySumbit(int sumbitStatus,
            int lastId, int pageSize)
    {
        ArrayList<Answer> workList = new ArrayList<Answer>();
        synchronized (SYNC_TAG)
        {
            getWritableDB();
            Cursor cur = null;

            String sql = "select * from " + StudyRecordTable.TABLE_NAME
                    + " where " + StudyRecordTable.SUBMIT_STATUS + " = ? "
                    + " order by " + StudyRecordTable.SUBMIT_DATE
                    + " desc limit 0, ? ";

            cur = db.rawQuery(sql,
                    new String[]{sumbitStatus + "", pageSize + ""});

            if (cur != null && cur.getCount() > 0)
            {
                while (cur.moveToNext())
                {
                    workList.add(getMyWork(cur));
                }
            }
            cur.close();
            dispose();
        }
        return workList;
    }

    /**
     * 查询所有的作业数据
     *
     * @param isOnline
     *            是否是线上作业
     * @return
     */
    public static ArrayList<Answer> queryAllWorkList(int isOnline)
    {
        ArrayList<Answer> workList = new ArrayList<Answer>();
        synchronized (SYNC_TAG)
        {
            getReadableDB();
            Cursor cur = null;
            cur = db.query(StudyRecordTable.TABLE_NAME,
                    StudyRecordTable.getColumns(),
                    StudyRecordTable.MODULE_ID
                            + (isOnline == 0 ? " = ?" : "!= ?"),
                    new String[]{ModuleInfo.ModuleInfoId.MODULE_ID_TEST + ""},
                    null, null, null);
            if (cur != null && cur.getCount() > 0)
            {
                while (cur.moveToNext())
                {
                    workList.add(getMyWork(cur));
                }
            }
            cur.close();
            dispose();
        }
        return workList;
    }

    /**
     * 某条answer是否存在评测失败的记录
     * 
     * @param workId
     * @return
     */
    public static Answer answerIsExistFail(String workId)
    {
        Answer answer = null;
        synchronized (SYNC_TAG)
        {
            getReadableDB();
            Cursor cur = null;
            cur = db.query(StudyRecordTable.TABLE_NAME,
                    StudyRecordTable.getColumns(),
                    StudyRecordTable.SUBMIT_STATUS + " = ? and "
                            + StudyRecordTable.WORK_ID + " = ?",
                    new String[]{Answer.SubmitStatus.WORK_NO_SCORE_FAILE + "",
                            workId},
                    null, null, null);
            if (cur != null && cur.getCount() > 0)
            {
                if (cur.moveToNext())
                {
                    answer = getMyWork(cur);
                }
            }
            cur.close();
            dispose();
        }
        return answer;
    }


    /**
     * 某条answer是否存在评测失败的记录
     *
     * @param saveId
     * @return
     */
    public static Answer answerIsExistFailById(String saveId)
    {
        Answer answer = null;
        synchronized (SYNC_TAG)
        {
            getReadableDB();
            Cursor cur = null;
            cur = db.query(StudyRecordTable.TABLE_NAME, StudyRecordTable.getColumns(), StudyRecordTable.SUBMIT_STATUS + " = ? and " + StudyRecordTable._ID + " = ?", new String[]{Answer.SubmitStatus.WORK_NO_SCORE_FAILE + "",
                            saveId},
                    null, null, null);

            if (cur != null && cur.getCount() > 0)
            {
                if (cur.moveToNext())
                {
                    answer = getMyWork(cur);
                }
            }else {

                cur = db.query(StudyRecordTable.TABLE_NAME, StudyRecordTable.getColumns(), StudyRecordTable.SUBMIT_STATUS + " = ? and " + StudyRecordTable._ID + " = ?", new String[]{Answer.SubmitStatus.WORK_SUBMIT_FAILE + "",
                                saveId},
                        null, null, null);
                if (cur != null && cur.getCount() > 0)
                {
                    if (cur.moveToNext())
                    {
                        answer = getMyWork(cur);
                    }
                }

            }
            cur.close();
            dispose();
        }
        return answer;
    }

    /**
     * 注销登录
     */
    public static void logOut()
    {
        db = null;
        helper = null;
    }
}
